import collections
from tool import *
import openpyxl
from autoWidth import CXlAutofit
import matplotlib.pyplot as plt
import matplotlib.ticker as mtick
import statistics
import os
import warnings

warnings.filterwarnings('ignore')
plt.rcParams['font.sans-serif'] = ['Arial Unicode MS']

# df.to_csv('out.csv', index=False)
if __name__ == '__main__':
    # isYearDiy = input('如需自定义时间段请输入1，否则输入2）'
    # while isYearDiy !=1 and isYearDiy!=2:
    # isYearDiy = input('如需自定义时间段请输入1，否则输入2）'
    # if isYearDiy == 1:
    # startYear = input("请输入起始年份（例如2024）:")
    # startMonth = input("请输入起始月份:（1月即输入1）")
    # endYear = input("请输入终止年份")
    # endMonth = input("请输入终止年份")
    # store = input(请输入终止月份的库存量)
    # expireTime = input('请输入商品保质期：（九个月就输入9，一年半就输入18个月）')
    # outputPrefix = '../output/'
    # outputFileName = input('请输入输入文件名例如(demo.xlsx ->后缀名.xlsx一定要输对，不然文件无法打开)')
    # outputFileName = outputPrefix + outputFileName
    expireTime = 10
    store = 1000
    # fileNames = ['demoData.xlsx','demoData.xlsx','demoData.xlsx','demoData.xlsx','demoData.xlsx']
    outputFileName = '../output/clean1.xlsx'
    prefix = '../data/'
    fileNames = os.listdir(prefix)
    # 起始行
    outRows = 1
    # 起始列
    outColumns = 1
    openyxl_createFile(outputFileName)
    wb = openpyxl.load_workbook(outputFileName)
    sheet = wb['Sheet']
    for finishIdx, filename in enumerate(fileNames):
        if filename.startswith('.'):
            continue
        filename = prefix + filename
        dfData = disposeData(filename)
        outColumns = 1
        dic, description, maxKey, minkey, billDicForTable, saleAndCostList = cleanData(dfData)
        # 读取输出的文件
        minArr = minkey.split('-')
        maxArr = maxKey.split('-')
        # 数据表格中的日期起始
        startYear, startMonth, endYear, endMonth = int(minArr[0]), int(minArr[1]), int(maxArr[0]), int(maxArr[1])
        tableColumns = ColumnPadding(startYear, startMonth, endYear, endMonth)
        # reverseTableColumns = tableColumns
        reverseTableColumns = tableColumns.copy()
        reverseTableColumns.reverse()
        locationDic = getLocationName()
        optionYxl(sheet, 'A' + str(outRows), description, font1, True, 'A' + str(outRows), 'AD' + str(outRows),
                  isDescription=True)
        outRows += 1
        # 写左边的columns
        # fillColumns 可抽象出来
        optionYxl(sheet, 'A' + str(outRows + 1), 'Invoice', font2, False)
        optionYxl(sheet, 'A' + str(outRows + 2), 'Bill', font2, False)
        optionYxl(sheet, 'A' + str(outRows + 3), 'Inventory Qty Adjust', font2, False)
        optionYxl(sheet, 'A' + str(outRows + 4), 'Credit Memo', font2, False)
        optionYxl(sheet, 'A' + str(outRows + 5), 'Percent / %', font2, False)
        # 写列名
        tableColumnsFull = tableColumns.copy()
        tableColumnsFull.append('total')
        for i in range(len(tableColumnsFull)):
            stationName = locationDic[outColumns + 1]
            outColumns += 1
            optionYxl(sheet, stationName + str(outRows), tableColumnsFull[i], font2, False, cellWidth=400,
                      columnName=stationName)
        outColumns = 2
        outRows += 1
        Bill = dic['Bill']
        Invoice = dic['Invoice']
        adjust = dic['Inventory Qty Adjust']
        creditMemo = dic['Credit Memo']

        # 把上面四个的值的值转换成List -> 画图用， 填表用的是dic
        InvoiceList = floatToInt(getListFromDic(Invoice, tableColumns))
        BillList = floatToInt(getListFromDic(Bill, tableColumns))
        percentList = getPercent(InvoiceList, sum(BillList))
        percentDic = getPercentDic(percentList, tableColumns)
        # 拿到库存数据
        storeList, storeDict = recoverStore(Invoice, Bill, reverseTableColumns, store)
        # 写入表格数据

        drawOne(InvoiceList, 'Invoice', BillList, 'Bill', storeList, '库存', percentList, '百分比',
                columnList=tableColumns, isDrawOne=True, figName='fig1.jpg')

        sheet.merge_cells(
            locationDic[outColumns] + str(outRows) + ':' + locationDic[outColumns + 1] + str(outRows))
        optionYxl(sheet, locationDic[outColumns] + str(outRows), '剩余保质期', font2)
        outRows += 1
        storeDic = calculateStoreDate(store, expireTime, Bill, reverseTableColumns)
        # 填写保质期表格
        fillRemainTimeTable(sheet, outRows, outColumns, locationDic, storeDic)
        outColumns += 2
        outRows -= 1
        insertFigToExcel(sheet, locationDic[outColumns], outRows - 1, 'fig1.jpg')

        outRows += max(max(len(billDicForTable.keys()), len(Invoice.keys())), 40) + 2
        # 拿图片数据  图片1 数据-》 InvoiceList ,BillList  PercentList storeList

        # 第三部分,重新计算行数
        saleOrigin = saleAndCostList['sale']
        costOrigin = saleAndCostList['cost']
        saleDic = saleOrigin.copy()
        costDic = costOrigin.copy()
        saleDicForTable3 = saleDic.copy()
        costDicForTable3 = costDic.copy()
        timeLineForTableThreeLeft = tableColumns.copy()
        timeLineForTableThreeLeft.append('共计：')
        saleDic['共计：'] = sum(saleDic.values())
        costDic['共计：'] = sum(costDic.values())
        profitDic = {}
        for idx, key in enumerate(saleDicForTable3.keys()):
            if key in costDicForTable3.keys():
                cost = costDicForTable3[key]
            else:
                cost = 0
            profitDic[key] = saleDicForTable3[key] - cost
        profitDicOrigin = profitDic.copy()
        profitDic['total/year'] = round(sum(profitDic.values()), 2)
        saleDicForTable3['total/year'] = round(sum(saleDicForTable3.values()), 2)
        costDicForTable3['total/year'] = round(sum(costDicForTable3.values()), 2)
        # 第三张表格位置
        outColumns = 2
        tableThreeColumnList = ['销售总额/月', '成本总额/月', '利润/月', 'Inventory Qty Adjust', 'Credit Memo']
        dataThreeDic = {'销售总额/月': saleDicForTable3, '成本总额/月': costDicForTable3, '利润/月': profitDic,
                        'Inventory Qty Adjust': adjust,
                        'Credit Memo': creditMemo}
        timeLineListTableThree = tableColumns.copy()
        timeLineListTableThree.append('total/year')
        fillTable(sheet, outColumns, outRows, description + '--销售额', tableThreeColumnList,
                  timeLineListTableThree,
                  dataThreeDic, locationDic)
        outRows += len(tableThreeColumnList) + 3

        outColumns = 2
        # 月平均销售额
        fillTableBelowMain(sheet, outRows, outColumns, locationDic, saleDic, '月平均销售额', timeLineForTableThreeLeft,
                           isDescription=True,
                           color='ef949f')
        outColumns += 2
        fillTableBelowMain(sheet, outRows, outColumns, locationDic, costDic, '月平均成本额', timeLineForTableThreeLeft,
                           isDescription=True,
                           color='f2b7af')
        outColumns += 2
        # 月平均销售额两个表格之间的相对位置，使右边表格相对居中

        # 拿到画图数据
        saleFigList = floatToInt(getListFromDic(saleOrigin, tableColumns))
        costFigList = floatToInt(getListFromDic(costOrigin, tableColumns))
        profitFigList = floatToInt(getListFromDic(profitDicOrigin, tableColumns))
        drawOne(saleFigList, '销售额', costFigList, '成本额', profitFigList, '利润', columnList=tableColumns,
                figName='fig2.jpg')
        # outRows += max(len(tableThreeColumnList),
        #                max(len(saleDic.keys()), len(costDic.keys()))) + 2
        insertFigToExcel(sheet, locationDic[outColumns], outRows, 'fig2.jpg')
        outRows += max(40, len(saleDic.keys()))
        # 第四部分 确定最新的行列数,插完图片要重新计算行数

        averageSale = {}
        averageCost = {}
        averageProfit = {}
        for idx, dateTime in enumerate(tableColumns):
            if Invoice[dateTime] == 0:
                averageSale[dateTime] = 0
            else:
                averageSale[dateTime] = round(saleOrigin[dateTime] / Invoice[dateTime], 2)
            if Bill[dateTime] == 0:
                averageCost[dateTime] = 0
            else:
                averageCost[dateTime] = round(costOrigin[dateTime] / Bill[dateTime], 2)
            averageProfit[dateTime] = round(averageSale[dateTime] - averageCost[dateTime], 2)
        # 画图原始数据
        averageSaleOrigin = averageSale.copy()
        averageCostOrigin = averageCost.copy()
        averageProfitOrigin = averageProfit.copy()
        timeLineForTableThreeRight = tableColumns.copy()
        timeLineForTableThreeRight.append('年平均售价/成本价')
        tableFourColumnList = ['平均销售价/月', '平均成本价/月', '平均单价利润/月']
        dataFourDic = {'平均销售价/月': averageSale, '平均成本价/月': averageCost, '平均单价利润/月': averageProfit}
        averageSale['年平均售价/成本价'] = round(statistics.mean(averageSale.values()), 2)
        averageCost['年平均售价/成本价'] = round(statistics.mean(averageCost.values()), 2)
        averageProfit['年平均售价/成本价'] = round(statistics.mean(averageProfit.values()), 2)
        outColumns = 2
        fillTable(sheet, outColumns, outRows, description + '--销售价', tableFourColumnList,
                  timeLineForTableThreeRight,
                  dataFourDic, locationDic, titleColor='ffc000')
        outRows += len(tableFourColumnList) + 3

        # 从第二格开始
        fillTableBelowMain(sheet, outRows, outColumns, locationDic, averageSale, '月平均销售价',
                           timeLineForTableThreeRight,
                           isDescription=True,
                           color='f2ba02')
        outColumns += 2
        fillTableBelowMain(sheet, outRows, outColumns, locationDic, averageCost, '月平均成本价',
                           timeLineForTableThreeRight,
                           isDescription=True,
                           color='ffc000')
        outColumns += 2
        # 月平均销售额两个表格之间的相对位置，使右边表格相对居中
        averageSaleOriginList = floatToInt(getListFromDic(averageSaleOrigin, tableColumns))
        averageCostOriginList = floatToInt(getListFromDic(averageCostOrigin, tableColumns))
        averageProfitOriginList = floatToInt(getListFromDic(averageProfitOrigin, tableColumns))
        drawOne(averageSaleOriginList, '月平均销售额', averageCostOriginList, '月平均成本额', averageProfitOriginList,
                '月平均利润', columnList=tableColumns, figName='fig3.jpg')
        insertFigToExcel(sheet, locationDic[outColumns], outRows, 'fig3.jpg')
        outRows += 40
        if finishIdx % 10 == 0 and finishIdx != 0:
            print('已完成' + str(finishIdx) + '个文件')
    wb.save(outputFileName)
    print('数据处理完成，开始调整文件内格式，请稍等，文件越大，等待时间越长')
    clx = CXlAutofit()
    clx.style_excel(excel_name=outputFileName, sheet_name='Sheet')
    print('文件已经处理完毕，请在output目录下进行查阅')
